<?php
namespace app\admin\controller;
use think\Controller;
include EXTEND_PATH.'phpexcel/PHPExcel.php';
use PHPExcel;
include EXTEND_PATH.'phpexcel/PHPExcel/IOFactory.php';
use PHPExcel_IOFactory;

class Excel extends Controller
{
    /**
     * 下载Excel
     * @param array $title  表头数组，必须
     * @param array $data   数据数组，必须
     * @param string $fileName  生成的文件名，必须
     * @param array $format 数据列格式设置，选填
     * 示例：
     * $title = ['第一列', '第二列', '第三列'];
     * $data = [
     *       ['001000', 'BBBB', 33.2],
     *       ['2000', 'BBBB', 44.2],
     *       ['003000', 'BBBB', 33.2],
     *       ['4000', 'BBBB', 33.2],
     *   ];
     * $format = ['s', 's', 'n'];
     */
    public function downLoad($title = [], $data = [], $fileName = '', $format = [])
    {
        // 设置表格信息
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()
                    ->setCreator("转弯")
                    ->setLastModifiedBy("转弯")
                    ->setTitle("转弯")
                    ->setCategory("");
        // 设置表头
        $index = 0;
        for ($column = 'A'; $column <= 'Z'; $column++) {
            if (!isset($title[$index]))
                break;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($column . '1', $title[$index]);
            $index++;
        }

        // 填入数据
        foreach ($data as $key => $item) {
            $index     = $key + 2;
            $itemIndex = 0;
            for ($column = 'A'; $column <= 'Z'; $column++) {
                if (!isset($item[$itemIndex]))
                    break;
                if (!empty($format)) {
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValueExplicit($column . $index, $item[$itemIndex], $format[$itemIndex]);
                } else {
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($column . $index, $item[$itemIndex]);
                }
                $itemIndex++;
            }
        }

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $fileName . '.xls"');
        header('Cache-Control: max-age=1');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }

}